package cn.melon.Helper;

import java.io.File;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import cn.melon.base.IBaseExtractor;
import net.ucanaccess.jdbc.UcanaccessSQLException;

/**
 * 该类负责数据库后端交互, 后期功能可能会有所补充
 * @author corvo
 * @param <T> 实体类
 */

public class DBHelper <T>{
    
    ClassLoader classLoader = getClass().getClassLoader();

    
    private String url;
    String path = this.getClass().getProtectionDomain().getCodeSource().getLocation().getFile(); // 当前jar包目录
    String parentFolder = new File(path).getParent();           // jar包父目录
//    private final String DATABASE = classLoader.getResource("database/new.accdb").getFile();
    private String DATABASE = parentFolder + "/./database/new.accdb";
    
    public DBHelper() {
        String filename;
        try {
            filename = java.net.URLDecoder.decode(DATABASE, "utf-8");
            url = "jdbc:ucanaccess://" + filename;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver").newInstance();

            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }

            url = "jdbc:mysql://139.129.4.159/melon?"
                    + "user=melon"
                    + "&password=2016"
                    + "&characterEncoding=utf-8"
                    + "&useUnicode=true";
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 执行sql语句
     * @param sql 带参数的sql语句
     * @param paras sql语句中的参数
     * @return 返回是否执行成功
     * @throws UcanaccessSQLException 数据库访问错误
     */
    public Boolean sqlExecute(String sql, String[] paras) throws UcanaccessSQLException{
        PreparedStatement ps = null;
        Connection ct = null;
        Boolean b = true;

        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

            ct = DriverManager.getConnection(url);
            ps = ct.prepareStatement(sql);

            for (int i = 0; i < paras.length; i++) {
                ps.setString(i + 1, paras[i]);
            }
            
            ps.execute();
            
        } catch (ClassNotFoundException e) {
            b = false;
            e.printStackTrace();
        } catch (SQLException e) {
            b = false;
            e.printStackTrace();
        } catch (Exception e) {
            b = false;
            e.printStackTrace();
        } finally {
            try {
                if (ct != null) ct.close();
                if (ps != null) ps.close();
                
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return b;
    }
    
    
    /**
     * 创建数据库表
     * @param sqlCreate 数据库创建
     * @return  代码执行成功与否
     */
    public Boolean onCreate(String sqlCreate) {
        PreparedStatement ps = null;
        Connection ct = null;
        Boolean b = true; 

        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

            ct = DriverManager.getConnection(url);
            ps = ct.prepareStatement(sqlCreate);
            ps.execute();

        } catch (ClassNotFoundException e) {
            b = false;
            e.printStackTrace();
        } catch (SQLException e) {
            b = false;
            e.printStackTrace();
        } catch (Exception e) {
            b = false;
            e.printStackTrace();
        } finally {
            try {
                if (ct != null) ct.close();
                if (ps != null) ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
		return b;
    }

    /**
     * 更新数据库
     * @param sql  带参数的sql语句
     * @param paras sql语句参数
     * @return 返回执行成功与否
     */
    public synchronized Boolean sqlUpdate(String sql, String[] paras){
        PreparedStatement ps = null;
        Connection ct = null;
        Boolean b = true;
        
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

            ct = DriverManager.getConnection(url);
            ps = ct.prepareStatement(sql);

            for (int i = 0; i < paras.length; i++) {
                ps.setString(i + 1, paras[i]);
            }
            ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            b = false;
            e.printStackTrace();
        } catch (SQLException e) {
            b = false;
            e.printStackTrace();
        } catch (Exception e) {
            b = false;
            e.printStackTrace();
        } finally {
            try {
                if (ct != null) ct.close();
                if (ps != null) ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        return b;
    }

    /**
     * 查询数据库表获取数据
     * @param sql  查询sql语句
     * @param paras sql语句参数
     * @param be   ResultSet集合解压对象
     * @return 返回查询对象列表
     * @throws UcanaccessSQLException 数据库访问错误
     */
    public List<T> sqlQuery(String sql, String[] paras, final IBaseExtractor<T> be) throws UcanaccessSQLException{
        PreparedStatement ps = null;
        Connection ct = null;
        ResultSet rs = null;
        
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

            ct = DriverManager.getConnection(url);
            ps = ct.prepareStatement(sql);

            for (int i = 0; i < paras.length; i++) {
                ps.setString(i + 1, paras[i]);
            }
            
            rs = ps.executeQuery();
            
            return be.convertToList(rs);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (ct != null) ct.close();
                if (ps != null) ps.close();
                if (rs != null) rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        return null;
    }
}
